/*************************************************************************
	> File Name: sqlite.c
	> Author: 
	> Mail: 
	> Created Time: 2018年10月31日 星期三 09时09分13秒
 ************************************************************************/

#include<stdio.h>
#include<stdlib.h>
#include<sqlite3.h>
#include"sqlite.h"

int InitDatbase()
{
    sqlite3 *db = NULL;
    char *zErrMsg = NULL;
    int result = 0;
    result = sqlite3_open("busdb.db",&db);
    if (SQLITE_OK != result || NULL == db)
    {
        fprintf(stderr,"can't open datbase:%s\r\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return FAIL;
    }
    printf("datbase busdb open success!\r\n");
    char *createCardTab = "CREATE TABLE IF NOT EXISTS Card(\
        ID INTEGER PRIMARY KEY,\
        CARDNUM TEXT NOT NULL,\
        CARDTYPE INTEGER)";
    char *createRecordTab = "CREATE TABLE IF NOT EXISTS Record(\
        ID INTEGER PRIMARY KEY,\
        CARDNUM TEXT NOT NULL,\
        TIME TimeStamp NOT NULL DEFAULT (datetime('now','localtime')))";
    char *createQRCodeTab = "CREATE TABLE IF NOT EXISTS QRCode(\
        ID INTEGER PRIMARY KEY,\
        CODE TEXT NOT NULL)";
    result = sqlite3_exec(db,createCardTab,NULL,NULL,&zErrMsg);
    if (SQLITE_OK != result)
    {
        printf("%s\r\n",zErrMsg);
        sqlite3_free(zErrMsg);
        zErrMsg = NULL;
        return FAIL;
    }
    result = sqlite3_exec(db,createRecordTab,NULL,NULL,&zErrMsg);
    if (SQLITE_OK != result)
    {
        printf("%s\r\n",zErrMsg);
        sqlite3_free(zErrMsg);
        zErrMsg = NULL;
        return FAIL;
    }
    result = sqlite3_exec(db,createQRCodeTab,NULL,NULL,&zErrMsg);
    if (SQLITE_OK != result)
    {
        printf("%s\r\n",zErrMsg);
        sqlite3_free(zErrMsg);
        zErrMsg = NULL;
        return FAIL;
    }
    printf("create table success!\r\n");
    char *insertCode = "INSERT OR IGNORE INTO QRCode(CODE) SELECT 'AHFUIWHAOH' AS code\
                        UNION SELECT 'BUIABFIWFBU' UNION SELECT 'BVUIRBOIVHOIE'\
                        UNION SELECT 'BVUISIVAUIHUI' UNION SELECT 'DUIWFUIHIWH'\
                        UNION SELECT 'FDUAISNVON' UNION SELECT 'FVYUBVIAVUJ'\
                        UNION SELECT 'HIOWHFOPOI' UNION SELECT 'IOPOJBVUIBE'\
                        UNION SELECT 'VUIOVBWIUOD'";
    result = sqlite3_exec(db,insertCode,NULL,NULL,&zErrMsg);
    if (SQLITE_OK != result)
    {
        printf("%s\r\n",zErrMsg);
        sqlite3_free(zErrMsg);
        zErrMsg = NULL;
        return FAIL;
    }
    return SUCCESS;
}

int record_card(char *cn)
{
    sqlite3 *db = NULL;
    int result = 0;
    char *zErrMsg = NULL;
    char recordCard[BUF_SIZE] = {0};
    sprintf(recordCard,"INSERT INTO Record(CARDNUM) VALUES(%s)",cn);
    result = sqlite3_open("busdb.db",&db);
    if (SQLITE_OK != result || NULL == db)
    {
        fprintf(stderr,"can't open datbase:%s\r\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return FAIL;
    }
    printf("datbase busdb open success!\r\n");
    result = sqlite3_exec(db,recordCard,NULL,NULL,&zErrMsg);
    if (SQLITE_OK != result)
    {
        printf("%s\r\n",zErrMsg);
        sqlite3_free(zErrMsg);
        zErrMsg = NULL;
        sqlite3_close(db);
        return FAIL;
    }
    sqlite3_free(zErrMsg);
    zErrMsg = NULL;
    printf("record card success!\r\n");
    return SUCCESS;
}

int clr_db()
{
    sqlite3 *db = NULL;
    int result = 0;
    char *zErrMsg = NULL;
    char *delete = "DELETE FROM Record";
    result = sqlite3_open("busdb.db",&db);
    if (SQLITE_OK != result || NULL == db)
    {
        fprintf(stderr,"can't open datbase:%s\r\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return FAIL;
    }
    printf("datbase busdb open success!\r\n");
    result = sqlite3_exec(db,delete,NULL,NULL,&zErrMsg);
    if (SQLITE_OK != result)
    {
        printf("%s\r\n",zErrMsg);
        sqlite3_free(zErrMsg);
        zErrMsg = NULL;
        return FAIL;
     }
    sqlite3_free(zErrMsg);
    zErrMsg = NULL;
    printf("Record table clear!\r\n");
    return SUCCESS;
}

int register_card(char *cn,int type)
{
    sqlite3 *db = NULL;
    int result = 0;
    char *zErrMsg = NULL;
    char regCard[BUF_SIZE] = {0};
    sprintf(regCard,"INSERT INTO Card(CARDNUM,CARDTYPE) VALUES(%s,%d)",cn,type);
    result = sqlite3_open("busdb.db",&db);
    if (SQLITE_OK != result || NULL == db)
    {
        fprintf(stderr,"can't open datbase:%s\r\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return FAIL;
    }
    printf("datbase busdb open success!\r\n");
    result = sqlite3_exec(db,regCard,NULL,NULL,&zErrMsg);
    if (SQLITE_OK != result)
    {
        printf("%s\r\n",zErrMsg);
        sqlite3_free(zErrMsg);
        zErrMsg = NULL;
        return FAIL;
    }
    sqlite3_free(zErrMsg);
    zErrMsg = NULL;
    printf("register card success!\r\n");
    return SUCCESS;
}

int is_valid_card(char *cn)
{    
    sqlite3 *db = NULL;
    int result = 0;
    char *zErrMsg = NULL;
    char **dbResult = NULL;
    int nRow = 0;
    int nColumn = 0;
    char seekCard[BUF_SIZE] = {0};
    sprintf(seekCard,"SELECT CARDNUM FROM Card WHERE CARDNUM = %s",cn);
    result = sqlite3_open("busdb.db",&db);
    if (SQLITE_OK != result || NULL == db)
    {
        fprintf(stderr,"can't open datbase:%s\r\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return FALSE;
    }
    printf("datbase busdb open success!\r\n");
    result = sqlite3_get_table(db,seekCard,&dbResult,&nRow,&nColumn,&zErrMsg);
    if (SQLITE_OK != result)
    {
        printf("%s\r\n",zErrMsg);
        sqlite3_free(zErrMsg);
        zErrMsg = NULL;
        sqlite3_close(db);
        return FALSE;
    }
    sqlite3_free(zErrMsg);
    zErrMsg = NULL;
    if (0 == nRow || 0 == nColumn)
    {
        return FALSE;
    }
    record_card(cn);
    return TRUE;
}

int is_seniors_card(char *cn)
{
    sqlite3 *db = NULL;
    int result = 0;
    char *zErrMsg = NULL;
    char **dbResult = NULL;
    int nRow = 0;
    int nColumn = 0;
    char seniorsCard[BUF_SIZE] = {0};
    sprintf(seniorsCard,"SELECT CARDTYPE FROM Card WHERE CARDNUM = %s AND CARDTYPE = 1",cn);
    result = sqlite3_open("busdb.db",&db);
    if (SQLITE_OK != result || NULL == db)
    {
        fprintf(stderr,"can't open datbase:%s\r\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return FALSE;
    }
    printf("datbase busdb open success!\r\n");
    result = sqlite3_get_table(db,seniorsCard,&dbResult,&nRow,&nColumn,&zErrMsg);
    if (SQLITE_OK != result)
    {
        printf("%s\r\n",zErrMsg);
        sqlite3_free(zErrMsg);
        zErrMsg = NULL;
        sqlite3_close(db);
        return FALSE;
    }
    sqlite3_free(zErrMsg);
    zErrMsg = NULL;
    if (0 == nRow || 0 == nColumn)
    {
        return FALSE;
    }
    return TRUE;
}

void select_table()
{
    sqlite3 *db = NULL;
    int result = 0;
    int i = 0;
    char *zErrMsg = NULL;
    char selectTable[] = "SELECT * FROM Record";
    char **dbResult = NULL;
    int nRow = 0;
    int nColumn = 0; 
    result = sqlite3_open("busdb.db",&db);
    if (SQLITE_OK != result || NULL == db)
    {
        fprintf(stderr,"can't open datbase:%s\r\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return ;
    }
    printf("datbase busdb open success!\r\n");
    result = sqlite3_get_table(db,selectTable,&dbResult,&nRow,&nColumn,&zErrMsg);
    if (SQLITE_OK != result)
    {
        printf("%s\r\n",zErrMsg);
        sqlite3_free(zErrMsg);
        zErrMsg = NULL;
        sqlite3_close(db);
        return ;
    }
    sqlite3_free(zErrMsg);
    zErrMsg = NULL;
    if (NULL != dbResult)
    {
        FILE *fp = fopen("bus.txt","wb+");
        if (NULL == fp)
        {
            printf("open file error!\r\n");
            return ;
        }
        for (i = 3;i < (nRow + 1) * nColumn;i++)
	{
	    fprintf(fp,"%s\n",dbResult[i]);
	    fflush(fp);
	}
        fclose(fp);
    }
    sqlite3_free_table(dbResult);
    dbResult = NULL;
    sqlite3_close(db);
}

int is_valid_QRcode(char *code)
{
    sqlite3 *db = NULL;
    int result = 0;
    char *zErrMsg = NULL;
    char **dbResult = NULL;
    int nRow = 0;
    int nColumn = 0;
    char seekCode[BUF_SIZE] = {0};
    sprintf(seekCode,"SELECT * FROM QRCode WHERE CODE = '%s' ",code);
    result = sqlite3_open("busdb.db",&db);
    if (SQLITE_OK != result || NULL == db)
    {
        fprintf(stderr,"can't open datbase:%s\r\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return FALSE;
    }
    printf("datbase busdb open success!\r\n");
    result = sqlite3_get_table(db,seekCode,&dbResult,&nRow,&nColumn,&zErrMsg);
    if (SQLITE_OK != result)
    {
        printf("%s\r\n",zErrMsg);
        sqlite3_free(zErrMsg);
        zErrMsg = NULL;
        sqlite3_close(db);
        return FALSE;
    }
    if (0 == nRow || 0 == nColumn)
    {
        return FALSE;
    }
    char recordQRcode[BUF_SIZE] = {0};
    sprintf(recordQRcode,"INSERT INTO Record(CARDNUM) VALUES('%s')",code);
    result = sqlite3_exec(db,recordQRcode,NULL,NULL,&zErrMsg);
    if (SQLITE_OK != result)
    {
        printf("%s",zErrMsg);
        sqlite3_free(zErrMsg);
        zErrMsg = NULL;
        sqlite3_close(db);
        return FALSE;
    }
    printf("record QRcode success!\r\n");
    return TRUE;
}

int get_ridership()
{
    sqlite3 *db = NULL;
    int result = 0;
    long count = 0;
    char *zErrMsg = NULL;
    char **dbResult = NULL;
    int nRow = 0;
    int nColumn = 0;
    char *statistics = "SELECT ID FROM Record";
    result = sqlite3_open("busdb.db",&db);
    if (SQLITE_OK != result || NULL == db)
    {
        fprintf(stderr,"can't open datbase:%s\r\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return 0;
    }
    result = sqlite3_get_table(db,statistics,&dbResult,&nRow,&nColumn,&zErrMsg);
    if (SQLITE_OK != result)
    {
        printf("%s\r\n",zErrMsg);
        sqlite3_free(zErrMsg);
        zErrMsg = NULL;
        sqlite3_close(db);
        return 0;
    }
    count = nRow;
    sqlite3_free_table(dbResult);
    dbResult = NULL;
    sqlite3_close(db);
    return count;
}
